package com.youhp.spire.xls.demo.Charts;

import com.spire.xls.*;

import java.awt.*;

public class ScatterChart {
    public static void main(String[] args) {
        //Create a workbook
        Workbook workbook = new Workbook();

        //Get the first sheet and set its name
        Worksheet sheet = workbook.getWorksheets().get(0);
        sheet.setName("Scatter Chart");

        //Set chart data
        CreateChartData(sheet);

        //Add a chart
        Chart chart = sheet.getCharts().add(ExcelChartType.ScatterMarkers);

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("B2:B10"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(1);
        chart.setTopRow(11);
        chart.setRightColumn(10);
        chart.setBottomRow(28);

        chart.setChartTitle("Scatter Chart");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        chart.getSeries().get(0).setCategoryLabels(sheet.getCellRange("A2:A10"));
        chart.getSeries().get(0).setValues(sheet.getCellRange("B2:B10"));

        //Add a trend line for the first series
        chart.getSeries().get(0).getTrendLines().add(TrendLineType.Exponential);

        chart.getPrimaryValueAxis().setTitle("Salary");
        chart.getPrimaryCategoryAxis().setTitle("Car Price");

        //Save the Excel file
        workbook.saveToFile("output/ScatterChart.xlsx", FileFormat.Version2010);
    }

    private static void CreateChartData(Worksheet sheet) {
        //Set value of specified cell
        sheet.getCellRange("A1").setValue("Y(Salary)");
        sheet.getCellRange("A2").setValue("42763");
        sheet.getCellRange("A3").setValue("195387");
        sheet.getCellRange("A4").setValue("35672");
        sheet.getCellRange("A5").setValue("217637");
        sheet.getCellRange("A6").setValue("74734");
        sheet.getCellRange("A7").setValue("130550");
        sheet.getCellRange("A8").setValue("42976");
        sheet.getCellRange("A9").setValue("15132");
        sheet.getCellRange("A10").setValue("54936");

        sheet.getCellRange("B1").setValue("X(Car Price)");
        sheet.getCellRange("B2").setValue("19455");
        sheet.getCellRange("B3").setValue("93965");
        sheet.getCellRange("B4").setValue("20858");
        sheet.getCellRange("B5").setValue("107164");
        sheet.getCellRange("B6").setValue("34036");
        sheet.getCellRange("B7").setValue("87806");
        sheet.getCellRange("B8").setValue("17927");
        sheet.getCellRange("B9").setValue("61518");
        sheet.getCellRange("B10").setValue("29479");

        //Style
        sheet.getCellRange("A1:B1").setColumnWidth(12);
        sheet.getCellRange("A1:B1").setRowHeight(15);
        sheet.getCellRange("A1:B1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:B1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:B1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:B1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        sheet.getCellRange("A2:B10").getCellStyle().setNumberFormat("\"$\"#,##0");
    }
}
